<?php
/**
 * Description of Emms_Report_ActiveLoansByFund
 *
 * @author andres
 */
class Emms_Report_ActiveLoansByFund extends HTech_Report
{
    protected function _setName() 
    {
        $this->_name = $this->translate('Active loans consolidated by fund');
    }
    
    protected function _setForm() 
    {
        $this->_form = new HTech_Report_Form_Date();
    }
    
    protected function _setIndex() 
    {
        $this->_index = 'FundId';
    }

    protected function _setSql() 
    {
        $this->_sql = array();
        
        $this->_sql[] = '
            select 
                lm.FundId,
                count(p.PaymentId) Payments,
                sum(p.Special) SpecialPayments,
                sum(if(p.Delay,1,0)) PaymentsBehindSchedule,
                sum(p.Delay) DaysBehindSchedule,
                sum(p.Principal) PrincipalPaid,
                sum(p.LoanInsurance) LoanInsurancePaid,
                sum(p.HealthInsurance) HealthInsurancePaid,
                sum(p.AdministrativeFee) AdministrativeFeePaid,
                sum(p.Interest) InterestPaid,
                sum(p.Penalties) PenaltiesPaid
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                payments p on p.LoanId = l.LoanId
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                lm.FundId';
        
        $this->_sql[] = '
            select 
                lm.FundId,
                sum(lwop.Amount) WrittenOffPaid
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                loan_write_off_payments lwop on lwop.LoanId = l.LoanId
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                lm.FundId';

        $this->_sql[] = '
            select 
                lm.FundId,
                f.Fund,
                count(l.LoanId) Loans,
                count(firsttime.LoanId) FirstTime,
                count(male.ClientId) Male,
                count(female.ClientId) Female,
                count(bank.LoanMasterId) Bank,
                count(individual.LoanMasterId) Individual,
                sum(l.Principal) PrincipalDisbursed,
                sum(l.LoanInsuranceMnt) LoanInsuranceDisbursed,
                sum(l.HealthInsuranceMnt) HealthInsuranceDisbursed,
                sum(l.AdministrativeFeeMnt) AdministrativeFeeDisbursed,
                sum(lwo.Principal) PrincipalWrittenOff,
                sum(lwo.Amount) AmountWrittenOff,
                count(disbursed.LoanId) Disbursed,
                count(paid.LoanId) Paid,
                count(wo.LoanId) WrittenOff,
                count(recovered.LoanId) Recovered,
                count(rural.CommunityId) Rural,
                count(urban.CommunityId) Urban,
                count(suburban.CommunityId) Suburban
            from
                loan_status_changes lsc1
            left join
                loan_status_changes lsc2 on 
                    lsc2.LoanId = lsc1.LoanId and 
                    lsc2.PreviousStatus = "Disbursed" and 
                    lsc2.Date <= :date
            left join
                loans l on l.LoanId = lsc1.LoanId
            left join
                loan_masters lm on lm.LoanMasterId = l.LoanMasterId
            left join
                funds f on f.FundId = lm.FundId
            left join
                clients male on male.ClientId = l.ClientId and male.Gender = "Male"
            left join
                clients female on female.ClientId = l.ClientId and female.Gender = "Female"
            left join
                loan_masters bank on bank.LoanMasterId = lm.LoanMasterId and bank.BorrowerType = "B"
            left join
                loan_masters individual on individual.LoanMasterId = lm.LoanMasterId and individual.BorrowerType = "I"
            left join
                loan_write_offs lwo on lwo.LoanId = l.LoanId
            left join
                loans disbursed on disbursed.LoanId = l.LoanId and disbursed.Status = "Disbursed"
            left join
                loans paid on paid.LoanId = l.LoanId and paid.Status = "Paid"
            left join
                loans wo on wo.LoanId = l.LoanId and wo.Status = "Written-Off"
            left join
                loans recovered on recovered.LoanId = l.LoanId and recovered.Status = "Recovered"
            left join
                communities rural on rural.CommunityId = lm.CommunityId and rural.Type = "Rural"
            left join
                communities urban on urban.CommunityId = lm.CommunityId and urban.Type = "Urban"
            left join
                communities suburban on suburban.CommunityId = lm.CommunityId and suburban.Type = "Suburban"
            left join
                loan_cycles firsttime on firsttime.LoanId = l.LoanId and firsttime.Cycle = 1
            where
                lsc1.Status = "Disbursed" and
                lsc1.Date <= :date and
                lsc2.Id is null
            group by
                f.FundId';
    }

    protected function _setHeaders()
    {
        $this->_headers = new stdClass();
        $this->_headers->Fund = $this->translate('Fund');
        $this->_headers->Loans = $this->translate('Loans');
        $this->_headers->FirstTime = $this->translate('First time loans');
        $this->_headers->Male = $this->translate('Male borrowers');
        $this->_headers->Female = $this->translate('Female borrowers');
        $this->_headers->Bank = $this->translate('Bank borrowers');
        $this->_headers->Individual = $this->translate('Individual borrowers');
        $this->_headers->Rural = $this->translate('Rural borrowers');
        $this->_headers->Urban = $this->translate('Urban borrowers');
        $this->_headers->Suburban = $this->translate('Sub-urban borrowers');
        $this->_headers->Disbursed = $this->translate('Currently active');
        $this->_headers->Paid = $this->translate('Currently paid');
        $this->_headers->WrittenOff = $this->translate('Currently written-off');
        $this->_headers->Recovered = $this->translate('Currently recovered');
        $this->_headers->PrincipalDisbursed = $this->translate('Principal disbursed');
        $this->_headers->LoanInsuranceDisbursed = $this->translate('Loan insurance disbursed');
        $this->_headers->HealthInsuranceDisbursed = $this->translate('Health insurance disbursed');
        $this->_headers->AdministrativeFeeDisbursed = $this->translate('Admin. fee disbursed');
        $this->_headers->Payments = $this->translate('Payments completed');
        $this->_headers->SpecialPayments = $this->translate('Special payments');
        $this->_headers->PaymentsBehindSchedule = $this->translate('Payments behind schedule');
        $this->_headers->DaysBehindSchedule = $this->translate('Paid days behind schedule');
        $this->_headers->PrincipalPaid = $this->translate('Principal paid');
        $this->_headers->LoanInsurancePaid = $this->translate('Loan insurance paid');
        $this->_headers->HealthInsurancePaid = $this->translate('Health insurance paid');
        $this->_headers->AdministrativeFeePaid = $this->translate('Admin. fee paid');
        $this->_headers->InterestPaid = $this->translate('Interest paid');
        $this->_headers->PenaltiesPaid = $this->translate('Penalties paid');
        $this->_headers->PrincipalWrittenOff = $this->translate('Principal written-off');
        $this->_headers->AmountWrittenOff = $this->translate('Total written-off');
        $this->_headers->WrittenOffPaid = $this->translate('Total recovered');
        $this->_headers->PrincipalBalance = $this->translate('Current principal balance');
    }
    
    public function setParams($params) 
    {
        $this->_params = array(':date' => $params['Date']);
    }
    
    protected function _process($data)
    {
        foreach($data as $row) 
            $data[$row->FundId]->PrincipalBalance = 
                $row->PrincipalDisbursed - $row->PrincipalPaid - $row->PrincipalWrittenOff;
        return $data;
    }

}
